package database;

import fingerprint.FingerPrint;
import fingerprint.ShazamHash;
import scanner.WaveFileReader;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static fingerprint.FFT.WINDOW_SIZE;
import static fingerprint.FFT.fft;


/**
 * Created by Alison on 2016/11/7.
 */
public class StoreFingerprint {

    //database info
    public static final String url = "jdbc:mysql://127.0.0.1:3306/fingerprint?user=root&autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=utf-8";
    public static final String user = "root";
    public static final String password = "abcde7758258";


    public static void main(String[] args) {

        //read and store names & fingerprints into database
        Store();
        //create index to increase enquiry speed
        createIndex();

    }


//get wav name list

    public static List<String> getAllNames(String filePath) {
        File file = null;
        file = new File(filePath);
        FileFilter fileFilter = new FileFilter("wav"); //文件的后缀名

        String[] fileNames = file.list(fileFilter);
        List<File> fileList = new ArrayList<File>();

        List<String> list = new ArrayList<String>();
        for (String path : fileNames)
            list.add(path);
        return list;
    }

    //store names & fingerprints into database
    public static void createIndex() {


        try {
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();

            //构造sql语句
            String sql1 = " CREATE INDEX fingerIndex ON song_finger (finger_id) ;";
            st.execute(sql1);

            con.close();
            System.out.println("Index created");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //存储歌曲指纹信息
    public static void StoreFingers(ArrayList<ShazamHash> m_hashes, String name, List<String> files, int id) {


        try {
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();

            //sql statements
            String sql1 = " INSERT INTO song(song_id,name) values(?,?);";
            String sql2 = " INSERT INTO song_finger(finger_id,song_id,offset) values(?,?,?);";

            PreparedStatement ps1 = con.prepareStatement(sql1);
            PreparedStatement ps2 = con.prepareStatement(sql2);

            con.setAutoCommit(false);

            ps1.setInt(1, id);
            ps1.setString(2, name);
            ps1.addBatch();

            for (int i = 0; i < m_hashes.size(); i++) {
                ps2.setInt(1, m_hashes.get(i).finger_id);
                ps2.setInt(2, id);
                ps2.setInt(3, m_hashes.get(i).offset);
                ps2.addBatch();
            }
            ps2.executeBatch();
            ps1.executeBatch();

            con.commit();
            st.close();
            con.close();
            System.out.println("Fingers Stored");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void Store() {
        long startTime = System.currentTimeMillis();   //获取开始时间

        final String path1 = "data/wav_part_5";

        List<String> lists = GetAllWavs(path1);
        List<String> names = getAllNames(path1);

        for (int m = 0; m < lists.size(); m++) {
            long startTime01 = System.currentTimeMillis();   //获取开始时间

            WaveFileReader id_x = new WaveFileReader(lists.get(m));
            //判断是否读取成功
            if (!id_x.isSuccess())
                return;
            double[] idx = id_x.getFinalData();
            ArrayList<double[]> slices = new ArrayList<double[]>();

            int number = id_x.getDataLen() / WINDOW_SIZE;
            double[] ret = new double[WINDOW_SIZE];
            FingerPrint song_idx = new FingerPrint(1);

            //利用傅里叶算法将时域信息转换成频域信息
            for (int i = 0; i < number; i++) {
                double[] slice = new double[WINDOW_SIZE];
                for (int j = 0; j < WINDOW_SIZE; j++) {
                    slice[j] = idx[WINDOW_SIZE * i + j];
                }
                ret = fft(slice);
                //提取出频率的N个最大值
                song_idx.append(ret);
                slices.add(ret);
            }
            //使用组合哈希算法，进行f1、f2、dt的获取
            //hashes是一个傅里叶list
            ArrayList<ShazamHash> hashes = new ArrayList<ShazamHash>();
            hashes = song_idx.combineHash();
            System.out.println("hash size:  " + hashes.size());
            long endTime03 = System.currentTimeMillis(); //获取结束时间

            System.out.println("finger time： " + (endTime03 - startTime01) / 1000.0 + "s");

            //
            StoreFingers(hashes, names.get(m), lists, m + 1);

            //

            long endTime02 = System.currentTimeMillis(); //获取结束时间
            System.out.println("第" + (m + 1) + "首歌曲耗时： " + (endTime02 - startTime01) / 1000.0 + "s");
        }
        long endTime = System.currentTimeMillis(); //获取结束时间
        System.out.println("插入总耗时： " + (endTime - startTime) / 1000.0 + "s");
        System.out.println("平均时长： " + (endTime - startTime) / (1000.0 * lists.size()) + "s");
    }

    //get wav paths list
    public static List<String> GetAllWavs(String filePath) {
        File file = null;
        file = new File(filePath);
        FileFilter fileFilter = new FileFilter("wav"); //文件的后缀名

        String[] fileNames = file.list(fileFilter);
        List<String> list = new ArrayList<String>();
        for (String path : fileNames)
            list.add(file.getAbsolutePath() + "/" + path);
        return list;
    }
    public static int getSongCounts() {

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        int count = 0;

        try {
            con = DriverManager.getConnection(url, user, password);
            st = con.createStatement();
            ResultSet rs2 = st.executeQuery("SELECT name, now()  FROM song;");


            ResultSet rs3 = st.executeQuery("SELECT  count(*) AS song FROM song;");
            while (rs3.next()) {
                count = rs3.getInt(1);
            }

            st.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }


}


